package cn.sfa.service.manage;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.rowset.serial.SerialException;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback;
import org.springframework.jdbc.support.lob.DefaultLobHandler;
import org.springframework.jdbc.support.lob.LobCreator;
import org.springframework.jdbc.support.lob.DefaultLobHandler;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import cn.sfa.cache.CacheFactory;
import cn.sfa.cache.ICacheHelper;
import cn.sfa.common.pagination.Pagination;
import cn.sfa.common.utils.CommonUtil;
import cn.sfa.common.utils.FilterSpesChar;
import cn.sfa.common.utils.MajorKeyGenerate;
import cn.sfa.common.utils.TableNameEnum;
import cn.sfa.entity.news.SaleNews;
import cn.sfa.entity.news.SaleNewsBranch;
import cn.sfa.entity.users.SaleUsersLogin;

/**
 * 
 * 类名称：NewsService 类描述： 公告相关服务类 创建人：LLY 创建时间：2012-2-15 下午11:12:18 修改备注：
 * 
 * @version
 */
@Service
@Transactional
public class NewsService {

	Logger logger = LoggerFactory.getLogger(NewsService.class);
	ICacheHelper cacheHelper = CacheFactory.CreateCacheHelper();

	@Autowired
	NamedParameterJdbcTemplate jdbcTemplate;

	@Autowired
	MajorKeyGenerate majorKeyGenerate;
	@Autowired
	private DefaultLobHandler lobHandler;

	/**
	 * 添加公告
	 * 
	 * @param saleNews
	 * @return boolean
	 * @throws SQLException
	 * @throws SerialException
	 */
	public boolean addNews(final SaleNews saleNews) {
		String sql = "insert into salenews("
				+ "SALENEWSID,CREATEDATE,CREATEUSER,UPDATEDATE,"
				+ "UPDATEUSER,NEWSTITLE,NEWSCONTENT," + "VISITCOUNT)"
				+ " values(default,?,?,?,?,?,?,?)";

		int count = jdbcTemplate.getJdbcOperations().execute(
				sql,
				new AbstractLobCreatingPreparedStatementCallback(
						this.lobHandler) {
					protected void setValues(PreparedStatement ps,
							LobCreator lobCreator) throws SQLException,
							DataAccessException {
						ps.setTimestamp(1, new Timestamp(saleNews
								.getCreateDate().getTime()));
						ps.setString(2, CommonUtil.jugeAndFixNull(saleNews
								.getCreateUid()));
						ps.setTimestamp(3, new Timestamp(saleNews
								.getUpdateDate().getTime()));
						ps.setString(4, CommonUtil.jugeAndFixNull(saleNews
								.getUpdateId()));

						ps.setString(5, CommonUtil.jugeAndFixNull(saleNews
								.getNewsTitle()));

						lobCreator.setClobAsString(ps, 6, saleNews
								.getNewsContent().replace("&lt;", "&amp;lt;")
								.replace("&gt;", "&amp;gt;")); // 设置详细的CLOB字段的内容

						ps.setInt(7, saleNews.getVisitCount());
					}
				});

		if (count > 0) {
			return true;
		}
		return false;
	}

	/**
	 * 查询出所有公告
	 * 
	 * @return List<SaleNews>
	 */
	public List<SaleNews> getNews() {
		String sql = "select * from salenews order by CREATEDATE DESC";
		List<SaleNews> news = new ArrayList<SaleNews>();
		news = jdbcTemplate.query(sql, new HashMap<String, Object>(),
				new BeanPropertyRowMapper<SaleNews>(SaleNews.class));
		return news;
	}

	/**
	 * 分页查询
	 */
	public Pagination searchNews(
			int currentPage,// 页码
			int numPerPage,// 页尺寸
			String url,// 分页url
			SaleNews saleNews, String beginCreateDate, String endCreateDate,
			SaleUsersLogin user) {

		StringBuilder sqlStr = new StringBuilder();
		sqlStr.append("select * from salenews s where 1=1 ");
		
		sqlStr.append("and (:newstitle is null or s.newstitle like :newstitle) ");
		sqlStr.append("and (:newscontent is null or s.newscontent like :newscontent) ");
		sqlStr.append("and (:begincreatedate is null or s.CREATEDATE >= date_format(:begincreatedate, '%Y-%m-%d')) ");
        sqlStr.append("and (:endcreatedate is null or s.CREATEDATE <= date_format(concat(:endcreatedate,' 23:59:59'),'%Y/%m/%d %T')) ");
		Map<String,Object> paramMap = new HashMap<String,Object>(); 
		paramMap.put("newstitle", CommonUtil.isEmpty(saleNews.getNewsTitle())?null:"%"+saleNews.getNewsTitle()+"%");
		paramMap.put("newscontent", CommonUtil.isEmpty(saleNews.getNewsContent())?null:"%"+saleNews.getNewsContent()+"%");
		paramMap.put("begincreatedate", CommonUtil.isEmpty(beginCreateDate)?null:beginCreateDate);
		paramMap.put("endcreatedate", CommonUtil.isEmpty(endCreateDate)?null:endCreateDate);
		 
		return new Pagination(sqlStr.toString(), paramMap, currentPage,
				numPerPage, url, jdbcTemplate);
	}

	public boolean deleteNewsById(String saleNewsId) {
		String sql = "delete from salenews where salenewsid=:saleNewsId";
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("saleNewsId", saleNewsId);
		int count = jdbcTemplate.update(sql, map);
		if (count > 0) {
			return true;
		}
		return false;
	}

	public SaleNews getNew(String saleNewsId) {
		String sql = "select * from salenews where SALENEWSID=:SALENEWSID ";
		final SaleNews news = new SaleNews();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("SALENEWSID", saleNewsId);
		jdbcTemplate.query(sql, paramMap, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				news.setSaleNewsId(rs.getString("salenewsid"));
				news.setCreateDate(rs.getDate("createDate"));
				news.setCreateUid(rs.getString("createuser"));
				news.setUpdateDate(rs.getDate("updatedate"));
				news.setUpdateId(rs.getString("updateuser"));
				news.setNewsTitle(rs.getString("newstitle"));
				String clobText = lobHandler.getClobAsString(rs, "NEWSCONTENT");
				news.setNewsContent(clobText);
			}

		});

		return news;
	}

	/**
	 * 编辑公告
	 * 
	 * @param saleNews
	 * @return boolean
	 * @throws SQLException
	 * @throws SerialException
	 */
	public boolean updateNews(final SaleNews saleNews) {
		String sql = "update salenews set "
				+ "UPDATEDATE=?,UPDATEUSER=?,"
				+ "NEWSTITLE=?,NEWSCONTENT=?"
				+ " where salenewsid=?";

		int count = jdbcTemplate.getJdbcOperations().execute(
				sql,
				new AbstractLobCreatingPreparedStatementCallback(
						this.lobHandler) {
					protected void setValues(PreparedStatement ps,
							LobCreator lobCreator) throws SQLException,
							DataAccessException {
						ps.setTimestamp(1, new Timestamp(saleNews
								.getUpdateDate().getTime()));
						ps.setString(2, CommonUtil.jugeAndFixNull(saleNews
								.getUpdateId()));
						ps.setString(3, CommonUtil.jugeAndFixNull(saleNews
								.getNewsTitle()));
						lobCreator.setClobAsString(ps, 4, saleNews
								.getNewsContent().replace("&lt;", "&amp;lt;")
								.replace("&gt;", "&amp;gt;")); // 设置详细的CLOB字段的内容

						ps.setString(5, CommonUtil.jugeAndFixNull(saleNews
								.getSaleNewsId()));
					}
				});

		if (count > 0) {
			return true;
		}
		return false;
	}

	/**
	 * 通过用户所属机构取得可读公告
	 * 
	 * @param saleNewsBranchId
	 * @return
	 */
	public Pagination getNewsByBranch(int currentPage,// 页码
			int numPerPage,// 页尺寸
			String url,// 分页url
			String saleNewsBranchId) {
		String sql = "select * from salenews sns order by createdate";
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("saleNewsBranchId", saleNewsBranchId);
		return new Pagination(sql, paramMap, currentPage, numPerPage, url,
				jdbcTemplate);
	}

}
